﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Webcar.BLL;
using Webcar.Entitys;
using Webcar.Utils;
using EntityFramework.Extensions;
using System.Data;
using System.Data.SqlClient;
namespace Webcar.Controllers
{
    public partial class TableController : BaseControler
    {
        public ActionResult RunRecordList()
        {
            return View();
        }
        public ActionResult RunRecordListPartialView(string id)
        {
            return PartialView();
        }
        public ActionResult RunRecordDelete(int id)
        {
            CurrentUser.EnsurePermission(PermissionManager.填写无功运行记录);
            using (var DB = new WebcarContext())
            {
                var model = DB.bs_car_runrecord.Find(id);
                if (model != null)
                {
                    DB.bs_car_runrecord.Remove(model);
                    DB.SaveChanges();
                }
                return Success("RunRecordList", "Table", new { id = model.dept_code });
            }
            
        }
        [HttpGet]
        public ActionResult RunRecordEdit(int id)
        {
            bs_car_runrecord model = null;
            if (id>0)
            {
                model = WebcarDataProvider.DB.bs_car_runrecord.Find(id);
            }
            if (model == null)
            {
                model = new bs_car_runrecord();
                model.run_date = DateTime.Now;
            }
            ViewBag.RecordNo = "";
            if (model.car_no.IsNotNull())
            {
                var car = WebcarDataProvider.DB.BS_CAR_RECORD.Find(model.car_no);
                if (car != null)
                {
                    ViewBag.RecordNo = car.RECORD_NO;
                }
            }

            return View(model: model);
        }
        [HttpPost]
        public ActionResult RunRecordEdit(bs_car_runrecord model)
        {
            CurrentUser.EnsurePermission(PermissionManager.填写无功运行记录);
            string error = "";
            if (CheckRunRecordModel(model, ref error) == false)
            {
                ViewBag.ErrorMessage = error;
                ViewBag.RecordNo =  Request.Form["RECORD_NO"];
                return View(model: model);
            }


            bs_car_runrecord item = null;
            using (var DB = new WebcarContext())
            {
                if (model.xh > 0)
                {
                    item = DB.bs_car_runrecord.Find(model.xh);
                }
                else
                {
                    item = new bs_car_runrecord();
                    DB.bs_car_runrecord.Add(item);
                }

                model.CopyPropertyTo(item);
                DB.SaveChanges();
            }
            return Success("RunRecordList", "Table", new { id = item.dept_code });
        }

        bool CheckRunRecordModel(bs_car_runrecord model, ref string error)
        {
            if (model.car_no.IsNull())
            {
                error = "请输入车牌号。";
                return false;
            }
            if (model.dept_code.IsNull())
            {
                error = "请选择部门。";
                return false;
            }
            if (model.driver_name.IsNull())
            {
                error = "请输入驾驶员。";
                return false;
            }
            if (model.run_place.IsNull())
            {
                error = "请输入运行地点。";
                return false;
            }
            if (model.run_date==null)
            {
                error = "请输入运行时间。";
                return false;
            }

            return true;
        }

        public ActionResult RunRecordSummary(string year,string quarter)
        {
            if (year.IsNull()) return View();
            string sql = @"select t.dept_code,t2.deptname,
                    sum(carnums_01) as carnums_01,
                    sum(carnums_02) as carnums_02,
                    sum(carnums_03) as carnums_03,
                    sum(month_01) as month_01,
                    sum(month_02) as month_02,
                    sum(month_03) as month_03,
                    sum(isnull(carnums_01,0)-isnull(month_01,0)) as nocheck_01,
                    sum(isnull(carnums_02,0)-isnull(month_02,0))  as nocheck_02,
                    sum(isnull(carnums_03,0)-isnull(month_03,0))as nocheck_03
                    from 
                    (
                    select b.dept_code,
                    sum(case when convert(varchar(7),b.buy_date,120)<@year+case when len(cast(1+@year2 as varchar))=1 then '-0' else '-' end+cast(1+@year2 as varchar) then 1 else 0 end) carnums_01,
                    sum(case when convert(varchar(7),b.buy_date,120)<@year+case when len(cast(2+@year2 as varchar))=1 then '-0' else '-' end+cast(2+@year2 as varchar) then 1 else 0 end) carnums_02,
                    sum(case when convert(varchar(7),b.buy_date,120)<@year+case when len(cast(3+@year2 as varchar))=1 then '-0' else '-' end+cast(3+@year2 as varchar) then 1 else 0 end) carnums_03,
                    0 month_01,0 month_02,0 month_03
                     from bs_car_record b
                    where b.car_class2 ='TZ'
                    group by b.dept_code
                    union all
                    select t.dept_code,
                    0,0,0,
                    sum(case run_date when @year+case when len(cast(1+@year2 as varchar))=1 then '-0' else '-' end+cast(1+@year2 as varchar) then 1 else 0 end) month_01,
                    sum(case run_date when @year+case when len(cast(2+@year2 as varchar))=1 then '-0' else '-' end+cast(2+@year2 as varchar) then 1 else 0 end) month_02,
                    sum(case run_date when @year+case when len(cast(3+@year2 as varchar))=1 then '-0' else '-' end+cast(3+@year2 as varchar) then 1 else 0 end) month_03
                    from (select distinct dept_code,car_no,convert(varchar(7),run_date,120)run_date from bs_car_runrecord) t
                    group by t.dept_code) as t,
                    sys_dept t2 where t.dept_code=t2.deptcode and t2.use_flag='T'
                    group by t.dept_code,t2.deptname";
            SqlParameter p1 = new SqlParameter("@year", year);
            SqlParameter p2 = new SqlParameter("@year2", quarter);
            DataTable table = WebcarDataProvider.DB.ExecuteDataTable(sql,p1,p2);

            return View(model: table);
        }
    }
}